{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# テーブル操作\n",
    "\n",
    "* 事前にjsd_developmetテーブルを作成してデータは投入してある\n",
    "\n",
    "```\n",
    "### テーブルの構造の表示\n",
    "jsd_development=> \\d people\n",
    "================================================\n",
    "                          Table \"public.people\"\n",
    " Column  |  Type   |                      Modifiers\n",
    "---------+---------+-----------------------------------------------------\n",
    " id      | integer | not null default nextval('people_id_seq'::regclass)\n",
    " name    | text    |\n",
    " age     | integer |\n",
    " address | text    |\n",
    "================================================\n",
    "\n",
    "### テーブル内のデータ一覧\n",
    "jsd_development=> SELECT * FROM people;\n",
    "================================================\n",
    " id | name | age | address\n",
    "----+------+-----+---------\n",
    "  1 | Taro |  21 | Tokyo\n",
    "(1 row)\n",
    "================================================\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## データ一覧の取得"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "---------------------------------------------------\n",
      "--- Rows:1\n",
      "---------------------------------------------------\n",
      "id:1\n",
      "name:Taro\n",
      "age:21\n",
      "address:Tokyo\n",
      "---------------------------------------------------\n",
      "--- Rows:2\n",
      "---------------------------------------------------\n",
      "id:2\n",
      "name:Hanako\n",
      "age:18\n",
      "address:Hokkaido\n",
      "---------------------------------------------------\n",
      "--- Rows:3\n",
      "---------------------------------------------------\n",
      "id:11\n",
      "name:Shiro\n",
      "age:15\n",
      "address:Ehime\n",
      "---------------------------------------------------\n",
      "--- Rows:4\n",
      "---------------------------------------------------\n",
      "id:9\n",
      "name:Ume\n",
      "age:19\n",
      "address:Wakayama\n"
     ]
    }
   ],
   "source": [
    "%%bash\n",
    "### 変数の設定\n",
    "name=PeopleSelect\n",
    "classpath=\".:/root/git_jupyter_notebook/Java/postgresql-42.1.1.jar\"\n",
    "#classpath=\".:/Users/ftakao2007/jupyter/jupyter_notebook/Java/postgresql-42.1.1.jar\"\n",
    "\n",
    "### ソースの編集\n",
    "cat <<- EOS > ${name}.java\n",
    "import java.sql.Connection;\n",
    "import java.sql.DriverManager;\n",
    "import java.sql.ResultSet;\n",
    "import java.sql.ResultSetMetaData;\n",
    "import java.sql.Statement;\n",
    "import java.util.ArrayList;\n",
    "import java.util.List;\n",
    "\n",
    "public class ${name} {\n",
    "  public static void main(String[] args) throws Exception{\n",
    "    Statement statement = null;\n",
    "    ResultSet resultSet = null;\n",
    "    Connection connection = null;\n",
    "    \n",
    "    try{\n",
    "      //# 接続\n",
    "      connection = DriverManager.getConnection(\"jdbc:postgresql://10.136.3.189:5432/jsd_development\", \"jsd\", \"pass\");\n",
    "      statement = connection.createStatement();\n",
    "      \n",
    "      //# SQL文発行\n",
    "      resultSet = statement.executeQuery(\"SELECT * FROM people\");\n",
    "\n",
    "      //# 値の取得\n",
    "      List<String> fields = new ArrayList<String>();\n",
    "      ResultSetMetaData rsmd = resultSet.getMetaData();\n",
    "      for (int i = 1; i <= rsmd.getColumnCount(); i++) {\n",
    "        fields.add(rsmd.getColumnName(i));\n",
    "      }\n",
    "\n",
    "      //# 結果の出力\n",
    "      int rowCount = 0;\n",
    "      while (resultSet.next()) {\n",
    "        rowCount++;\n",
    "\n",
    "        System.out.println(\"---------------------------------------------------\");\n",
    "        System.out.println(\"--- Rows:\" + rowCount);\n",
    "        System.out.println(\"---------------------------------------------------\");\n",
    "\n",
    "        //# 値は、「resultSet.getString(<フィールド名>)」で取得する。\n",
    "        for (String field : fields) {\n",
    "          System.out.println(field + \":\" + resultSet.getString(field));\n",
    "        }\n",
    "      }\n",
    "    } finally {\n",
    "      //# 接続を切断する\n",
    "      if (resultSet != null) {\n",
    "        resultSet.close();\n",
    "      }\n",
    "      if (statement != null) {\n",
    "        statement.close();\n",
    "      }\n",
    "      if (connection != null) {\n",
    "        connection.close();\n",
    "      }\n",
    "    }\n",
    "  }\n",
    "}\n",
    "EOS\n",
    "\n",
    "### コンパイル\n",
    "#javac ${name}.java\n",
    "javac -encoding UTF-8 ${name}.java\n",
    "\n",
    "### 実行\n",
    "java -classpath ${classpath} ${name}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## データの検索"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "---------------------------------------------------\n",
      "--- Rows:1\n",
      "---------------------------------------------------\n",
      "id:2\n",
      "name:Hanako\n",
      "age:18\n",
      "address:Hokkaido\n"
     ]
    }
   ],
   "source": [
    "%%bash\n",
    "### 変数の設定\n",
    "name=PostgresTest\n",
    "classpath=\".:/root/git_jupyter_notebook/Java/postgresql-42.1.1.jar\"\n",
    "\n",
    "### ソースの編集\n",
    "cat <<- EOS > ${name}.java\n",
    "import java.sql.Connection;\n",
    "import java.sql.DriverManager;\n",
    "import java.sql.ResultSet;\n",
    "import java.sql.ResultSetMetaData;\n",
    "import java.sql.Statement;\n",
    "import java.util.ArrayList;\n",
    "import java.util.List;\n",
    "\n",
    "public class ${name} {\n",
    "  public static void main(String[] args) throws Exception{\n",
    "    Connection connection = null;\n",
    "    Statement statement = null;\n",
    "    ResultSet resultSet = null;\n",
    "    \n",
    "    try{\n",
    "      //# 接続\n",
    "      connection = DriverManager.getConnection(\"jdbc:postgresql://10.136.3.189:5432/jsd_development\", \"jsd\", \"pass\");\n",
    "      statement = connection.createStatement();\n",
    "      \n",
    "      //# SQL文発行\n",
    "      resultSet = statement.executeQuery(\"SELECT * FROM people WHERE id = 2\");\n",
    "\n",
    "      //# 値の取得\n",
    "      List<String> fields = new ArrayList<String>();\n",
    "      ResultSetMetaData rsmd = resultSet.getMetaData();\n",
    "      for (int i = 1; i <= rsmd.getColumnCount(); i++) {\n",
    "        fields.add(rsmd.getColumnName(i));\n",
    "      }\n",
    "\n",
    "      //# 結果の出力\n",
    "      int rowCount = 0;\n",
    "      while (resultSet.next()) {\n",
    "        rowCount++;\n",
    "\n",
    "        System.out.println(\"---------------------------------------------------\");\n",
    "        System.out.println(\"--- Rows:\" + rowCount);\n",
    "        System.out.println(\"---------------------------------------------------\");\n",
    "\n",
    "        //# 値は、「resultSet.getString(<フィールド名>)」で取得する。\n",
    "        for (String field : fields) {\n",
    "          System.out.println(field + \":\" + resultSet.getString(field));\n",
    "        }\n",
    "      }\n",
    "            \n",
    "    } finally {\n",
    "      //# 接続を切断する\n",
    "      if (resultSet != null) {\n",
    "        resultSet.close();\n",
    "      }\n",
    "      if (statement != null) {\n",
    "        statement.close();\n",
    "      }\n",
    "      if (connection != null) {\n",
    "        connection.close();\n",
    "      }\n",
    "    }\n",
    "  }\n",
    "}\n",
    "EOS\n",
    "\n",
    "### コンパイル\n",
    "#javac ${name}.java\n",
    "javac -encoding UTF-8 ${name}.java\n",
    "\n",
    "### 実行\n",
    "java -classpath ${classpath} ${name}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## データの登録"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%bash\n",
    "### 変数の設定\n",
    "name=PostgresTest\n",
    "classpath=\".:/root/git_jupyter_notebook/Java/postgresql-42.1.1.jar\"\n",
    "\n",
    "### ソースの編集\n",
    "cat <<- EOS > ${name}.java\n",
    "import java.sql.Connection;\n",
    "import java.sql.DriverManager;\n",
    "import java.sql.ResultSet;\n",
    "import java.sql.ResultSetMetaData;\n",
    "import java.sql.Statement;\n",
    "import java.util.ArrayList;\n",
    "import java.util.List;\n",
    "\n",
    "public class ${name} {\n",
    "  public static void main(String[] args) throws Exception{\n",
    "    Connection connection = null;\n",
    "    Statement statement = null;\n",
    "    ResultSet resultSet = null;\n",
    "    \n",
    "    try{\n",
    "      //# 接続\n",
    "      connection = DriverManager.getConnection(\"jdbc:postgresql://10.136.3.189:5432/jsd_development\", \"jsd\", \"pass\");\n",
    "      statement = connection.createStatement();\n",
    "      \n",
    "      //# SQL文発行。insertやupdateやdeleteは「executeQuery」ではなく「executeUpdate」を使う\n",
    "      statement.executeUpdate(\"INSERT INTO people (name, age, address) VALUES ('Hanako', 18, 'Hokkaido')\");\n",
    "            \n",
    "    } finally {\n",
    "      //# 接続を切断する\n",
    "      if (resultSet != null) {\n",
    "        resultSet.close();\n",
    "      }\n",
    "      if (statement != null) {\n",
    "        statement.close();\n",
    "      }\n",
    "      if (connection != null) {\n",
    "        connection.close();\n",
    "      }\n",
    "    }\n",
    "  }\n",
    "}\n",
    "EOS\n",
    "\n",
    "### コンパイル\n",
    "#javac ${name}.java\n",
    "javac -encoding UTF-8 ${name}.java\n",
    "\n",
    "### 実行\n",
    "java -classpath ${classpath} ${name}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## データの登録(コマンドライン引数を利用)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%bash\n",
    "### 変数の設定\n",
    "name=PostgresTest\n",
    "classpath=\".:/root/git_jupyter_notebook/Java/postgresql-42.1.1.jar\"\n",
    "\n",
    "### ソースの編集\n",
    "cat <<- EOS > ${name}.java\n",
    "import java.sql.Connection;\n",
    "import java.sql.DriverManager;\n",
    "import java.sql.ResultSet;\n",
    "import java.sql.ResultSetMetaData;\n",
    "import java.sql.Statement;\n",
    "import java.util.ArrayList;\n",
    "import java.util.List;\n",
    "\n",
    "public class ${name} {\n",
    "  public static void main(String[] args) throws Exception{\n",
    "    Connection connection = null;\n",
    "    Statement statement = null;\n",
    "    ResultSet resultSet = null;\n",
    "    \n",
    "    if (args.length != 3) {\n",
    "      System.out.println(\"usage: java -classpath .:/root/git_jupyter_notebook/Java/postgresql-42.1.1.jar PostgresTest name age address\");\n",
    "      System.exit(1);\n",
    "    }\n",
    "    \n",
    "    try{\n",
    "      //# 接続\n",
    "      connection = DriverManager.getConnection(\"jdbc:postgresql://10.136.3.189:5432/jsd_development\", \"jsd\", \"pass\");\n",
    "      statement = connection.createStatement();\n",
    "      \n",
    "      //# SQL文発行\n",
    "      String name = args[0];\n",
    "      int age = Integer.parseInt(args[1]);\n",
    "      String address = args[2];\n",
    "      statement.executeUpdate(\"INSERT INTO people (name, age, address) VALUES ('\" + name + \"', \" + age + \", '\" + address + \"')\");\n",
    "\n",
    "    } finally {\n",
    "      //# 接続を切断する\n",
    "      if (resultSet != null) {\n",
    "        resultSet.close();\n",
    "      }\n",
    "      if (statement != null) {\n",
    "        statement.close();\n",
    "      }\n",
    "      if (connection != null) {\n",
    "        connection.close();\n",
    "      }\n",
    "    }\n",
    "  }\n",
    "}\n",
    "EOS\n",
    "\n",
    "### コンパイル\n",
    "#javac ${name}.java\n",
    "javac -encoding UTF-8 ${name}.java\n",
    "\n",
    "### 実行。ここで名前、年齢、住所を引数で与える\n",
    "java -classpath ${classpath} ${name} Jiro 23 Osaka"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## データの削除"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%bash\n",
    "### 変数の設定\n",
    "name=PostgresTest\n",
    "classpath=\".:/root/git_jupyter_notebook/Java/postgresql-42.1.1.jar\"\n",
    "\n",
    "### ソースの編集\n",
    "cat <<- EOS > ${name}.java\n",
    "import java.sql.Connection;\n",
    "import java.sql.DriverManager;\n",
    "import java.sql.ResultSet;\n",
    "import java.sql.ResultSetMetaData;\n",
    "import java.sql.Statement;\n",
    "import java.util.ArrayList;\n",
    "import java.util.List;\n",
    "\n",
    "public class ${name} {\n",
    "  public static void main(String[] args) throws Exception{\n",
    "    Connection connection = null;\n",
    "    Statement statement = null;\n",
    "    ResultSet resultSet = null;\n",
    "    \n",
    "    try{\n",
    "      //# 接続\n",
    "      connection = DriverManager.getConnection(\"jdbc:postgresql://10.136.3.189:5432/jsd_development\", \"jsd\", \"pass\");\n",
    "      statement = connection.createStatement();\n",
    "      \n",
    "      //# SQL文発行。insertやupdateやdeleteは「executeQuery」ではなく「executeUpdate」を使う\n",
    "      statement.executeUpdate(\"DELETE FROM people WHERE id = 5\");\n",
    "            \n",
    "    } finally {\n",
    "      //# 接続を切断する\n",
    "      if (resultSet != null) {\n",
    "        resultSet.close();\n",
    "      }\n",
    "      if (statement != null) {\n",
    "        statement.close();\n",
    "      }\n",
    "      if (connection != null) {\n",
    "        connection.close();\n",
    "      }\n",
    "    }\n",
    "  }\n",
    "}\n",
    "EOS\n",
    "\n",
    "### コンパイル\n",
    "#javac ${name}.java\n",
    "javac -encoding UTF-8 ${name}.java\n",
    "\n",
    "### 実行\n",
    "java -classpath ${classpath} ${name}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 以下実験用"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "connection = org.postgresql.jdbc.PgConnection@46f7f36a\n",
      "statement = org.postgresql.jdbc.PgStatement@7aec35a\n",
      "resultSet = org.postgresql.jdbc.PgResultSet@6e5e91e4\n",
      "\n",
      "1Taro21Tokyo\n",
      "2Hanako18Hokkaido\n",
      "6Hanako18Hokkaido\n"
     ]
    }
   ],
   "source": [
    "%%bash\n",
    "### 変数の設定\n",
    "name=PostgresTest\n",
    "classpath=\".:/root/git_jupyter_notebook/Java/postgresql-42.1.1.jar\"\n",
    "\n",
    "### ソースの編集\n",
    "cat <<- EOS > ${name}.java\n",
    "import java.sql.Connection;\n",
    "import java.sql.DriverManager;\n",
    "import java.sql.ResultSet;\n",
    "import java.sql.ResultSetMetaData;\n",
    "import java.sql.Statement;\n",
    "import java.util.ArrayList;\n",
    "import java.util.List;\n",
    "\n",
    "public class ${name} {\n",
    "  public static void main(String[] args) throws Exception{\n",
    "    Connection connection = null;\n",
    "    Statement statement = null;\n",
    "    ResultSet resultSet = null;\n",
    "    \n",
    "    try{\n",
    "      //# 接続\n",
    "      connection = DriverManager.getConnection(\"jdbc:postgresql://10.136.3.189:5432/jsd_development\", \"jsd\", \"pass\");\n",
    "      System.out.println(\"connection = \" + connection);\n",
    "      \n",
    "      //# Statementオブジェクトを生成\n",
    "      statement = connection.createStatement();\n",
    "      System.out.println(\"statement = \" + statement);\n",
    "\n",
    "      //# SQL文発行。結果の表をresultSetに格納\n",
    "      resultSet = statement.executeQuery(\"SELECT * FROM people\");\n",
    "      System.out.println(\"resultSet = \" + resultSet);\n",
    "      \n",
    "      System.out.println();\n",
    "      \n",
    "      while(resultSet.next()){\n",
    "        //# resultSetからテーブルの値を取得。テーブル名をgetInt, getStringの引数に入れる事でデータを取得できる\n",
    "        int id = resultSet.getInt(\"id\");\n",
    "        String name = resultSet.getString(\"name\");\n",
    "        int age = resultSet.getInt(\"age\");\n",
    "        String address = resultSet.getString(\"address\");\n",
    "        \n",
    "        System.out.println(id + name + age + address);\n",
    "      }\n",
    "\n",
    "    } finally {\n",
    "      //# 接続を切断する\n",
    "      if (resultSet != null) {\n",
    "        resultSet.close();\n",
    "      }\n",
    "      if (statement != null) {\n",
    "        statement.close();\n",
    "      }\n",
    "      if (connection != null) {\n",
    "        connection.close();\n",
    "      }\n",
    "    }\n",
    "  }\n",
    "}\n",
    "EOS\n",
    "\n",
    "### コンパイル\n",
    "#javac ${name}.java\n",
    "javac -encoding UTF-8 ${name}.java\n",
    "\n",
    "### 実行\n",
    "java -classpath ${classpath} ${name}"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
